﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using OfficeOpenXml;
using System.Web;
using System.IO;

public partial class PengadaanBarang : System.Web.UI.Page
{
    private static string strConn = ConfigurationManager.ConnectionStrings["LabConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            populateTahunAnggaran();
        }
    }

    private void populateData()
    {
        if (string.IsNullOrEmpty(lstTahun.SelectedValue))
        {
            return;
        }

        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter("sp_get_list_permintaan_tahunan_per_lab " + lstTahun.SelectedValue, strConn))
        {
            da.Fill(dt);
        }
        userList.DataSource = dt;
        userList.DataBind();

        if (dt.Rows.Count < 1)
        {
            showMessage("Tidak ada data", "Tidak ada daftar permintaan yang harus disetujui untuk tahun anggaran " + lstTahun.SelectedValue, "warning");
        }
    }

    private void populateTahunAnggaran()
    {
        string query = "sp_get_tahun_anggaran";
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(query, strConn))
        {
            da.Fill(dt);
        }
        DataTable tahun = new DataTable();
        tahun.Columns.Add("TAHUN");
        if (dt.Rows.Count > 0)
        {
            DataRow dr = tahun.NewRow();
            dr["TAHUN"] = "";
            tahun.Rows.Add(dr);

            int min = int.Parse(dt.Rows[0]["TAHUN1"].ToString());
            int max = int.Parse(dt.Rows[0]["TAHUN2"].ToString());
            for (int i = min; i <= max; i++)
            {
                dr = tahun.NewRow();
                dr["TAHUN"] = i;
                tahun.Rows.Add(dr);
            }
        }
        lstTahun.DataSource = tahun;
        lstTahun.DataValueField = "TAHUN";
        lstTahun.DataTextField = "TAHUN";
        lstTahun.DataBind();
    }

    private void showMessage(string caption, string message, string type)
    {
        message = message.Replace("'", "&apos;");
        message = message.Replace(Environment.NewLine, "<br />");
        string clientScript = "$(document).ready(function(){ new PNotify({ title: '" + caption + "', text: '" + message + "', type: '" + type + "',styling: 'bootstrap3' }); });";
        ScriptManager.RegisterStartupScript(Page, this.GetType(), "ShowMessage", clientScript, true);
    }

    protected void lstTahun_SelectedIndexChanged(object sender, EventArgs e)
    {
        populateData();
    }

    protected void btnCetak_Click(object sender, EventArgs e)
    {
        //using OfficeOpenXml;
        try
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter("sp_get_list_permintaan_tahunan_per_lab " + lstTahun.SelectedValue, strConn))
            {
                da.Fill(dt);
            }

            ExcelPackage excel = new ExcelPackage();
            var workSheet = excel.Workbook.Worksheets.Add("Pengadaan Barang T/A " + lstTahun.SelectedValue.ToString());
            var totalCols = dt.Columns.Count;
            var totalRows = dt.Rows.Count;
            
            for (var i = 1; i <= totalCols; i++)
            {
                workSheet.Cells[1, i].Value = dt.Columns[i - 1].ColumnName;
            }
            for (var j = 1; j <= totalRows; j++)
            {
                for (var i = 1; i <= totalCols; i++)
                {
                    var text = dt.Rows[j - 1][i - 1].ToString();

                    workSheet.Cells[j + 1, i].Value = HttpUtility.HtmlDecode(text);
                }
            }
            using (var memoryStream = new MemoryStream())
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=PengadaanBarang_" + lstTahun.SelectedValue.ToString() + ".xlsx");
                excel.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
        catch (Exception ex)
        {
            showMessage("ERROR MENYIMPAN FILE EXCEL", ex.Message, "error");
        }
    }
}